from openpyxl import load_workbook

from execelUtil.common import commonConstants as comconsts, commonUtil as comutil
from mysqlUtil import connectmysql as myslutil

filename = "d:\\tmp\\headtransit\\20240829.xlsx"

workbook = load_workbook(filename)
table_columns = ["sku_code", "old_sku_code", "platform", "store", "ship_qty", "recv_qty", "wh_code", "bill_no"]
datamaping = {
    "cinvcode": "sku_code",
    "oldsku": "old_sku_code",
    "actualShipmentQty": "ship_qty",
    "receivedQty": "recv_qty",
    "purposeDepotCode": "wh_code",
    "shipmentPlanId": "bill_no"
}

head_row = {}
resList = []
i = 0
# 选择工作表
sheet = workbook.active
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, values_only=True):
    if row[0] is None:
        break
    sku_qty = {}

    if i == 0:
        j = 0
        for prop in row:
            # print(prop)
            j += 1
            head_row[prop] = j - 1
    else:
        print(head_row)
        for prop in head_row :
            sku_qty[prop] = row[head_row[prop]]
        cinvCode = sku_qty[datamaping["cinvcode"]]
        # print(cinvCode)
        ship_nos = sku_qty["transit_no"]
        if ship_nos is not None:
            ship_nos = ship_nos.split(",")
            for ship_no in ship_nos:
                paras = comutil.getApiList(comconsts.HEAD_DETAIL_PARA_BODY)
                paras[comconsts.HEAD_DETAIL_PARA_BODY_SHIPMENTPLANID] = ship_no
                resp = comutil.getBillListsWithGet(comconsts.HEAD_DETAIL_URL, paras)
                for data in resp[comconsts.DATA]:
                    # print(data)
                    if data["cinvcode"] == sku_qty[datamaping["cinvcode"]] and data["oldsku"] == sku_qty[
                        datamaping["oldsku"]] and data["purposeDepotCode"] == sku_qty[datamaping["purposeDepotCode"]]:
                        sku_qty[datamaping["actualShipmentQty"]] = data["actualShipmentQty"] if data["actualShipmentQty"] is not None else 0
                        sku_qty[datamaping["receivedQty"]] = data["receivedQty"] if data["receivedQty"] is not None else 0
                        sku_qty[datamaping["purposeDepotCode"]] = data["purposeDepotCode"]
                        sku_qty[datamaping["shipmentPlanId"]] = data["shipmentPlanId"]
                myslutil.save_data("head_transit",sku_qty,table_columns)
                print(resp)
        # print(head_row)

        # skucode = row[head_row['sku_code']]
        # skucode = row[head_row['old_sku_code']]
        # skucode = row[head_row['sku_code']]
        # skucode = row[head_row['sku_code']]
        # print(skucode)
        # qty = int(row[head_row['actualQty']])
        # print(qty)
        # if skucode in sku_qty:
        #     sku_qty[skucode] += qty
        # else:
        #     sku_qty[skucode] = qty
    resList.append(sku_qty)
    i += 1
    # if i > 3:
    #     break
# print(resList)
